﻿using System;
using Microsoft.Office.Interop.Excel;

namespace WinformMDB
{
    class ExcelOperation
    {
        /// <summary>
        /// datatable快速转换excel
        /// </summary>
        /// <param name="excelTable">数据表</param>
        /// <param name="filePath">带文件名路径</param>
        public static void SuperToExcel(System.Data.DataTable excelTable, string filePath)
        {
            Application app = new Application();
            //可视弹窗，屏幕跟新
            app.Visible = false;
            app.ScreenUpdating = false;
            //设置禁止弹出保存和覆盖的询问提示框 
            app.DisplayAlerts = false;
            app.AlertBeforeOverwriting = false;

            //Application对象 -> Workbook对象(Excel工作薄) -> Worksheet对象(Excel工作表)
            Workbook wBook = app.Workbooks.Add(true);
            Worksheet wSheet = wBook.Worksheets[1] as Worksheet;
            //范围
            Range range;
            //行列总数
            int colCount = excelTable.Columns.Count;
            int rowCount = excelTable.Rows.Count;

            //Application.DoEvents();//好像可以防止弹窗未响应
            
            //创建缓存数据
            object[,] objData = new object[rowCount + 1, colCount];

            try
            {
                //写第一行列名
                int size = excelTable.Columns.Count;
                for (int i = 0; i < size; i++)
                {
                    wSheet.Cells[1, 1 + i] = excelTable.Columns[i].ToString();
                }
                range = (Range)wSheet.Range[app.Cells[1, 1], app.Cells[1, colCount]];//设置工作表第一行的范围
                range.Interior.ColorIndex = 15;//背景色 灰色
                range.Font.Bold = true;//粗字体
            }
            catch (Exception colErr)
            {
                throw new Exception("导出Excel出错！创建列名失败，错误原因：" + colErr.Message);
            }

            try
            { 
                //写表中数据
                for (int rowIndex = 0; rowIndex < rowCount; rowIndex++)
                {
                    for (int colIndex = 0; colIndex < colCount; colIndex++)
                    {
                        objData[rowIndex, colIndex] = excelTable.Rows[rowIndex][colIndex].ToString().Trim();
                    }
                }
                //写入Excel 
                range = (Range)wSheet.Range[app.Cells[2, 1], app.Cells[rowCount + 1, colCount]];
                range.NumberFormatLocal = "@";//设置数字文本格式
                range.Value2 = objData;
                wSheet.Columns.AutoFit();//列的宽度自适应
            }
            catch (Exception writeErr)
            {
                throw new Exception("导出Excel出错！写入数据失败，错误原因：" + writeErr.Message);
            }

            try
            {
                wBook.Saved = true;
                wBook.SaveCopyAs(filePath);

                app.Quit();
                app = null;
                GC.Collect();
            }
            catch(Exception saveErr)
            {
                throw new Exception("导出Excel出错！保存文件失败，错误原因：" + saveErr.Message);
            }

        }
        
    }
}
